Database Management System
Q151.
Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?Q1 : Select e.empId From employee e Where not exists (Select * From employee s where s.department = "5" and s.salary >=e.salary) Q2 : Select e.empId From employee e Where e.salary > Any (Select distinct salary From employee s Where s.department ="5"Q152.
Consider the relation account (customer, balance) where customer is a primary key and there are no mall values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broken but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned. Consider these statements about Query 1 and Query 2. 1. Query 1will produce the same row set as Query 2 for some but not all databases 2. Both Query 1 Query 2 are correct implementations of the specification 3. Query 1 is a correct implementation of the specification but Query 2 is not 4. Neither query 1 nor Query 2 is a correct implementation of the specification 5. Assigning rank with a pure relational Query takes less time than scanning in decreasing balance order the assigning ranks using ODBC Which two of the above statements are correct?Q153.
Consider the relation enrolled (student, course) in which student, course) is the primary key, and the relation paid (student, amount) where student is the primary key . Assume no null values and no foreign keys or integrity constraints. Given the following four queries: Query 1: Select from enrolled where student in (select student form paid) Query 2: Select student from paid where student in (select student from enrolled) Query 3: Select E. student from enrolled E, paid P where E. student= P student Query 4: Select student from paid where exists (select*from enrolled where enrolled student=paid.student) Which one of the following statements is correct?Q154.
Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1,Y=1) is inserted in the table. Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X=7;Q155.
Consider two tables in a relational database with columns and rows as follows: \overset{\text{Table: Student}}{\begin{array}{|c|c|c|} \hline \textbf {Roll\_no} & \textbf{Name} & \textbf{Dept\_id} \\\hline 1& \text{ABC} & 1\\\hline 2& \text{DEF} & 1 \\\hline 3 & \text{GHI} & 2\\\hline 4 & \text{JKL} & 3\\\hline \end{array}} \qquad \overset{\text{Table: Department}}{\begin{array}{|c|c|c|} \hline \textbf {Dept\_id} & \textbf{Dept\_name} \\\hline 1& \text{A} \\\hline 2& \text{B} \\\hline 3 & \text{C} \\\hline \end{array}} Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id What will happen if we try to execute the following two SQL statements? update Student set Dept_id = Null where Roll_on = 1 update Department set Dept_id = Null where Dept_id = 1Q156.
A table T1 in a relational database has the following rows and columns: \begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1& 10 \\\hline 2 & 20 \\\hline3 & 30 \\\hline 4 & \text{NULL}\\\hline \end{array} The following sequence of SQL statements was successfully executed on table T1. Update T1 set marks = marks + 5 Select avg(marks) from T1 What is the output of the select statement?Q157.
A relational schema for a train reservation database is given below What pids are returned by the following SQL query for the above instance of the tables? SELECT pid FROM Re servation WHERE class 'AC' AND EXISTS (SELECT * FROM Passenger WHERE age > 65 AND Passenger.pid Reservation.pid)Q158.
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database. \overset{\text{D: Drivers relation}}{\begin{array}{|c|l|r|c|}\hline \textbf{did}& \textbf{dname}& \textbf{rating}& \textbf{age} \\\hline 22& \text{Karthikeyan}& 7& 25 \\ \hline 29& \text{Salman}& 1& 33 \\ \hline 31& \text{Boris}& 8& 55 \\\hline 32& \text{Amoldt}& 8& 25 \\\hline 58& \text{Schumacher}& 10& 35 \\\hline 64& \text{Sachin}& 7& 35 \\\hline 71& \text{Senna}& 10& 16 \\\hline 74& \text{Sachin}& 9& 35 \\\hline 85& \text{Rahul}& 3& 25 \\\hline 95& \text{Ralph}& 3& 53 \\\hline \end{array}} \qquad \overset{\text{R: Reserves relation}}{\begin{array}{|c|c|c|}\hline \textbf {did} & \textbf {Cid} & \textbf {day} \\\hline 22 & 101 & 10-10-06 \\ \hline 22 & 102 & 10-10-06\\ \hline 22 & 103 & 08-10-06 \\\hline 22 & 104 & 07-10-06 \\\hline 31 & 102 & 10-11-16 \\\hline 31&103 &06-11-16 \\\hline 31 & 104&12-11-16 \\\hline 64 & 101 &05-09-06 \\\hline 64& 102 & 08-09-06 \\\hline 74 & 103 & 08-09-06 \\\hline \end{array}} \overset{\text{C: Cars relation}}{\begin{array}{|c|c|c|c|}\hline \textbf {Cid} & \textbf {Cname} & \textbf{colour} \\\hline 101 & \text{Renault} & \text{blue} \\ \hline 102 & \text{Renault} & \text{red} \\ \hline 103 & \text{Ferrari} & \text{green} \\\hline 104 & \text{Jaguar} & \text{red} \\\hline \end{array}} select D.dname from Drivers D where D.did in ( select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'red' intersect select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'green' ) Let n be the number of comparisons performed when the above SQL query is optimally executed. If linear search is used to locate a tuple in a relation using primary key, then n lies in the range:Q159.
The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price > B.price) < 5Q160.
Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database. \overset{\text{D: Drivers relation}}{\begin{array}{|c|l|r|c|}\hline \textbf{did}& \textbf{dname}& \textbf{rating}& \textbf{age} \\\hline 22& \text{Karthikeyan}& 7& 25 \\ \hline 29& \text{Salman}& 1& 33 \\ \hline 31& \text{Boris}& 8& 55 \\\hline 32& \text{Amoldt}& 8& 25 \\\hline 58& \text{Schumacher}& 10& 35 \\\hline 64& \text{Sachin}& 7& 35 \\\hline 71& \text{Senna}& 10& 16 \\\hline 74& \text{Sachin}& 9& 35 \\\hline 85& \text{Rahul}& 3& 25 \\\hline 95& \text{Ralph}& 3& 53 \\\hline \end{array}} \qquad \overset{\text{R: Reserves relation}}{\begin{array}{|c|c|c|}\hline \textbf {did} & \textbf {Cid} & \textbf {day} \\\hline 22 & 101 & 10-10-06 \\ \hline 22 & 102 & 10-10-06\\ \hline 22 & 103 & 08-10-06 \\\hline 22 & 104 & 07-10-06 \\\hline 31 & 102 & 10-11-16 \\\hline 31&103 &06-11-16 \\\hline 31 & 104&12-11-16 \\\hline 64 & 101 &05-09-06 \\\hline 64& 102 & 08-09-06 \\\hline 74 & 103 & 08-09-06 \\\hline \end{array}} \overset{\text{C: Cars relation}}{\begin{array}{|c|c|c|c|}\hline \textbf {Cid} & \textbf {Cname} & \textbf{colour} \\\hline 101 & \text{Renault} & \text{blue} \\ \hline 102 & \text{Renault} & \text{red} \\ \hline 103 & \text{Ferrari} & \text{green} \\\hline 104 & \text{Jaguar} & \text{red} \\\hline \end{array}} What is the output of the following SQL query? select D.dname from Drivers D where D.did in ( select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'red' intersect select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'green' )